﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
///DBClass 的摘要说明
/// </summary>
public class DBClass
{
    public SqlConnection conn;

    public DBClass()
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
    }

    public int ExecuteSql(String cmdText)
    {
        conn.Open();
        SqlCommand comm = new SqlCommand(cmdText, conn);
        int x = comm.ExecuteNonQuery();
        conn.Close();

        return x;
    }

    public int ExecuteTransaction(String cmdText1, String cmdText2)
    {
        /*
         返回值：1-执行成功，0-执行失败
         参考链接：https://blog.csdn.net/ajg87686/article/details/102245373?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-1.control&dist_request_id=1331302.9875.16183000631065631&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-1.control
         */

        int ret = 0;

        conn.Open();
        SqlTransaction tran = conn.BeginTransaction();  //启动一个事务
        SqlCommand comm = new SqlCommand(); //为事务创建一个命令
        comm.Connection = conn;
        comm.Transaction = tran;

        try
        {
            comm.CommandText = cmdText1;
            comm.ExecuteNonQuery();

            comm.CommandText = cmdText2;
            comm.ExecuteNonQuery();

            tran.Commit(); //现在才开始执行哦
            ret = 1;
        }
        catch (Exception e)
        {
            tran.Rollback();
            ret = 0;
        }
        finally
        {
            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }

        return ret;
    }

    public DataTable GetRecords(String sqltext)
    {
        //conn.Open();
        SqlDataAdapter da = new SqlDataAdapter(sqltext, conn);
        DataTable dt = new DataTable();
        da.Fill(dt);
        //conn.Close();
        return dt;
    }
}